package database;

import java.io.IOException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import pages.LMUtils;

/**
 * Servlet implementation class CreateDB
 */
public class CreateDB extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public CreateDB() {
		super();
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {

		String error = null;
		try {
			String pass = request.getParameter("pass");
			byte[] passBytes = pass.getBytes();
			MessageDigest algorithm = MessageDigest.getInstance("MD5");
			algorithm.reset();
			algorithm.update(passBytes);
			byte messageDigest[] = algorithm.digest();

			StringBuffer hexString = new StringBuffer();
			for (int i = 0; i < messageDigest.length; i++) {
				String hex = Integer.toHexString(0xFF & messageDigest[i]);
				if (hex.length() == 1) {
					hexString.append('0');
				}
				hexString.append(hex);

			}
			String passMD5 = hexString.toString();
			if (passMD5.equals("aaad33e9ae09fe775b24f84fffceb240")) {
				createDB();
				error = "DONE";
			} else {
				error = passMD5;
			}
		} catch (NoSuchAlgorithmException e) {
			error = "Can't check password";
		} catch (NullPointerException e) {
			error = "No password provided.";
		} catch (DBException e) {
			error = "Error creating DB: " + e.getMessage();
		}
		response.getOutputStream().print(error);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
	}

	/**
	 * Drops the old database and creates a new one
	 * 
	 * @throws DBException
	 * 
	 * @throws LmPropertiesException
	 * @throws SQLException
	 */
	private void createDB() throws DBException {
		createTables();
	}

	/**
	 * Doesn't try to delete tables. Creates only non-existent tables
	 * 
	 * @throws SQLException
	 * @throws LmPropertiesException
	 */
	private void createTables() throws DBException {
		Connection dbConnection = null;
		PreparedStatement dropTableStmt = null;
		PreparedStatement createTableStmt = null;
		PreparedStatement insertAdminStmt = null;

		try {
			String userTableName = LMUtils.getContextParameterValue(
					getServletContext(), "db.UsersTableName");
			String rolesTableName = LMUtils.getContextParameterValue(
					getServletContext(), "db.RolesTableName");
			String typesTableName = LMUtils.getContextParameterValue(
					getServletContext(), "db.TypesTableName");
			String permissionsTableName = LMUtils.getContextParameterValue(
					getServletContext(), "db.PermissionsTableName");
			String instrumentsTableName = LMUtils.getContextParameterValue(
					getServletContext(), "db.InstrumentsTableName");
			String reservationsTableName = LMUtils.getContextParameterValue(
					getServletContext(), "db.ReservationsTableName");
			String xslsTableName = LMUtils.getContextParameterValue(
					getServletContext(), "db.XSLsTableName");

			dbConnection = DBUtils.getConnection();

			// Dropping all the tables
			dropTableStmt = dbConnection
					.prepareStatement("DROP TABLE IF EXISTS " + userTableName
							+ ", " + rolesTableName + ", " + typesTableName
							+ ", " + permissionsTableName + ", "
							+ instrumentsTableName + ", "
							+ reservationsTableName + ", " +xslsTableName);
			dropTableStmt.execute();
			// Creating Users table
			createTableStmt = dbConnection
					.prepareStatement("CREATE TABLE IF NOT EXISTS `"
							+ userTableName
							+ "` ("
							// +
							// "`userID` int(10) unsigned NOT NULL AUTO_INCREMENT,"
							+ "`Login` varchar(255) COLLATE utf8_bin NOT NULL"
							+ ", `Password` varchar(32) COLLATE utf8_bin NOT NULL"
							+ ", `Name` varchar(255) COLLATE utf8_bin NOT NULL"
							+ ", `Phone` varchar(255) COLLATE utf8_bin NOT NULL"
							+ ", `Address` varchar(255) COLLATE utf8_bin NOT NULL"
							+ ", `groupOwnerLogin` varchar(255) COLLATE utf8_bin NOT NULL"
							// + ", `isActive` tinyint(1) NOT NULL DEFAULT '1'"
							// +
							// "`type` enum('RESEARCHER','ADMIN') COLLATE utf8_bin NOT NULL DEFAULT 'RESEARCHER',"
							// + "PRIMARY KEY (`userID`),"
							+ ", PRIMARY KEY (`Login`)"
							+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;");
			createTableStmt.execute();

			// Creating Roles table
			createTableStmt = dbConnection
					.prepareStatement("CREATE TABLE IF NOT EXISTS `"
							+ rolesTableName
							+ "` ("
							+ "`Login` varchar(255) NOT NULL,"
							+ "`Role` enum('RESEARCHER','ADMIN', 'BLOCKED') NOT NULL DEFAULT 'RESEARCHER',"
							+ "`RealRole` enum('RESEARCHER','ADMIN') NOT NULL DEFAULT 'RESEARCHER',"
							+ "PRIMARY KEY (`Login`)"
							+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;");
			createTableStmt.execute();

			/*
			 * // Creating Templates table createTableStmt = dbConnection
			 * .prepareStatement("CREATE TABLE IF NOT EXISTS `" +
			 * templatesTableName + "` (" + " `Name` VARCHAR( 255 ) NOT NULL ,"
			 * + "`Content` TEXT NOT NULL ," + "PRIMARY KEY ( `Name` )" +
			 * ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;");
			 * createTableStmt.execute();
			 */
			// Creating Types table
			createTableStmt = dbConnection
					.prepareStatement("CREATE TABLE `"
							+ typesTableName
							+ "` ("
							+ "`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,"
							+ "`name` VARCHAR( 255 ) NOT NULL ,"
							//+ "`description` TEXT NOT NULL, "
							+ "UNIQUE KEY `name` (`name`)"
							+ ")  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;");
			createTableStmt.execute();

			// Creating Permissions table
			createTableStmt = dbConnection.prepareStatement("CREATE TABLE `"
					+ permissionsTableName + "` ("
					+ "`userLogin` varchar(255) NOT NULL ,"
					+ "`typeID` INT UNSIGNED NOT NULL ,"
					+ "PRIMARY KEY ( `userLogin` , `typeID` )"
					+ ") ENGINE = InnoDB;");
			createTableStmt.execute();

			// Creating Instruments table
			createTableStmt = dbConnection.prepareStatement("CREATE TABLE `"
					+ instrumentsTableName + "` ("
					+ "`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,"
					+ "`Name` varchar(255) NOT NULL ,"
					+ "`typeID` INT UNSIGNED NOT NULL ,"
					+ "`timeSlot` INT UNSIGNED NOT NULL ,"
					+ "`description` TEXT NOT NULL ,"
					+ "`messageForUsers` TEXT NOT NULL ,"
					+ "`isActive` BOOL NOT NULL," +
						"FULLTEXT (Name)," +
						"FULLTEXT (description)"
					+ ")   ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;");
			createTableStmt.execute();

			// Creating Reservations table
			createTableStmt = dbConnection
					.prepareStatement("CREATE TABLE `"
							+ reservationsTableName
							+ "` ("
							+ "`userLogin` varchar(255) NOT NULL  ,"
							+ "`instrumentID` INT UNSIGNED NOT NULL ,"
							+ "`startTime` TIMESTAMP NOT NULL ,"
							+ "`endTime` TIMESTAMP NOT NULL ,"
							+ "PRIMARY KEY ( `userLogin` , `instrumentID` , `startTime` )"
							+ ") ENGINE = InnoDB;");
			createTableStmt.execute();

			// creating XSLs table
			createTableStmt = dbConnection
					.prepareStatement("CREATE TABLE `"
							+ xslsTableName
							+ "` ("
							+ "`name` VARCHAR( 255 ) NOT NULL ,"
							+ "`xmlType` ENUM( 'Users', 'Instruments', 'Reservations' ) NOT NULL ,"
							+ "`content` TEXT NOT NULL ,"
							+ "PRIMARY KEY ( `name` , `xmlType` )"
							+ ") ENGINE = InnoDB;");
			createTableStmt.execute();

			// inserting admin
			insertAdminStmt = dbConnection
					.prepareStatement("INSERT INTO `"
							+ userTableName
							+ "` (`Login`, `Password`, `Name`, `groupOwnerLogin`, `Phone`, `Address`)"
							+ "VALUES ('admin', MD5('admin'), 'Admin', '', '' , '');");
			insertAdminStmt.execute();

			insertAdminStmt = dbConnection.prepareStatement("INSERT INTO `"
					+ rolesTableName + "` (`Login`, `Role`, `RealRole`)"
					+ "VALUES ('admin', 'ADMIN', 'ADMIN');");
			insertAdminStmt.execute();

			dbConnection.close();
		} catch (SQLException e) {
			throw new DBException("Can't create table: " + e.getMessage(), e);
		} catch (Exception e) {
			throw new DBException("Can't create table: " + e.getMessage(), e);
		} finally {
			DBUtils.close(dropTableStmt);
			DBUtils.close(createTableStmt);
			DBUtils.close(insertAdminStmt);
			DBUtils.close(dbConnection);
		}
	}
}
